package com.neusoft.news.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neusoft.news.bean.News;
import com.neusoft.news.bean.User;
import com.neusoft.news.utils.JDBCUtils;



public class UserDaoImpl implements UserDao{
	
	
	@Override
	public User getUser(User user) throws SQLException{
		//TODO
		StringBuffer sql = new StringBuffer("SELECT * FROM \"d_user\" WHERE 1=1");
		boolean flag = false;
		
		String username = user.getUserName();	
		if(username != null && !username.isEmpty()){
			sql.append(" AND \"user_name\"='"+username+"'");
			flag = true;
		}
		String password = user.getUserPwd();
		if(password != null && !password.isEmpty()){
			sql.append(" AND \"user_pwd\"='"+password+"'");
			flag = true;
		}
		int userid = user.getUserId();
		if(userid != 0){
			sql.append(" AND \"user_id\"="+userid+"");
			flag = true;
		}
		if(!flag) {
			return null;
		}
		
		User user1 = null;
	
		try {
			PreparedStatement pstmt = JDBCUtils.getConnection().prepareStatement(sql.toString());
			ResultSet rs  = pstmt.executeQuery();
			if(rs.next()) {
				user1 = new User();
				user1.setUserId(rs.getInt("user_id"));
				user1.setUserName(rs.getString("user_name"));
				user1.setUserPwd(rs.getString("user_pwd"));
				user1.setUserSex(rs.getString("user_sex"));
				user1.setUserBrithday(rs.getString("user_brithday"));
				user1.setUserTel(rs.getString("user_tel"));
				user1.setUserAddr(rs.getString("user_addr"));
				user1.setUserLevel(rs.getString("user_level"));					
			}
			rs.close();
			pstmt.close();
			
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}
		return user1;
	}

	@Override
	public boolean updateUser(User user) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("UPDATE \"d_user\" SET ");
		boolean flag = false;
		String username = user.getUserName();	
		if(username != null && !username.isEmpty()){
			sql.append(" \"user_name\"='"+username+"'");
			flag = true;
		}
		String usersex = user.getUserSex();
		if(usersex != null && !usersex.isEmpty()) {
			sql.append(", \"user_sex\"='"+usersex+"'");
			flag = true;
		}
		String brithday = user.getUserBrithday();
		if(brithday != null && !brithday.isEmpty()) {
			sql.append(", \"user_brithday\"='"+brithday+"'");
			flag = true;
		}
		String tel = user.getUserTel();
		if(tel != null && !tel.isEmpty()) {
			sql.append(", \"user_tel\"='"+tel+"'");
			flag = true;
		}
		String address = user.getUserAddr();
		if(address != null && !address.isEmpty()) {
			sql.append(", \"user_addr\"='"+address+"'");
			flag = true;
		}
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		sql.append(", \"update_date\"='"+df.format(new Date())+"'");
		int userid = user.getUserId();
		if(userid != 0) {
			sql.append(" WHERE \"user_id\"="+userid+"");
			flag = true;
		}
		if(!flag) {
			return false;
		}
		
		User user1 = null;
	
		try {
			PreparedStatement pstmt = JDBCUtils.getConnection().prepareStatement(sql.toString());
			int row = pstmt.executeUpdate();
			if(row != 0) {
				flag = true;
			}else {
				flag = false;
			}
			pstmt.close();		
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}
		return flag;
	}

	@Override
	public boolean changePwd(User user) throws SQLException {
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer("UPDATE \"d_user\" SET ");
				boolean flag = false;
				String password = user.getUserPwd();	
				if(password != null && !password.isEmpty()){
					sql.append(" \"user_pwd\"='"+password+"'");
					flag = true;
				}
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				sql.append(", \"update_date\"='"+df.format(new Date())+"'");
				int userid = user.getUserId();
				if(userid != 0) {
					sql.append(" WHERE \"user_id\"="+userid+"");
					flag = true;
				}
				if(!flag) {
					return false;
				}
				
				User user1 = null;
			
				try {
					PreparedStatement pstmt = JDBCUtils.getConnection().prepareStatement(sql.toString());
					int row = pstmt.executeUpdate();
					if(row != 0) {
						flag = true;
					}else {
						flag = false;
					}
					pstmt.close();		
				} catch (SQLException | ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					JDBCUtils.destroy();
				}
				return flag;
	}

	@Override
	public boolean insertUser(User user) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO \"d_user\"(\"user_name\",\"user_pwd\",\"user_sex\",\"user_brithday\",\"user_tel\",\"user_addr\",\"user_level\",\"update_date\",\"user_shinsa\") VALUES (?,?,?,?,?,?,?,?,?)";
		boolean flag = false;
		int row = -1;
		int idx = 1;

		try {
			PreparedStatement pstmt = JDBCUtils.getConnection().prepareStatement(sql);
			pstmt.setString(idx, user.getUserName());
			pstmt.setString(++idx, user.getUserPwd());
			pstmt.setString(++idx, user.getUserSex());
			pstmt.setString(++idx, user.getUserBrithday());
			pstmt.setString(++idx, user.getUserTel());
			pstmt.setString(++idx, user.getUserAddr());
			pstmt.setString(++idx, user.getUserLevel());
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			pstmt.setString(++idx, df.format(new Date()));
			pstmt.setString(++idx, user.getUserShinsa());
			row = pstmt.executeUpdate();
			if(row > 0) {
				flag = true;
			}else {
				flag = false;
			}
			pstmt.close();		
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}
		return flag;
	}

	@Override
	public List<User> selectOldUser() throws SQLException {
		// TODO Auto-generated method stub
		List<User> users = new ArrayList<User>();
		String sql = "SELECT * FROM \"d_user\" WHERE \"user_shinsa\"='1'";
		PreparedStatement pstmt;
		try {
			pstmt = JDBCUtils.getConnection().prepareStatement(sql);
			ResultSet rs  = pstmt.executeQuery();
			while(rs.next()){
				User user = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9));
				users.add(user);
			}
			rs.close();
			pstmt.close();
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}	
		return users;
	}

	@Override
	public boolean removeUser(int userid) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("DELETE FROM \"d_user\" WHERE \"user_id\"=?");
		boolean result = false;
		PreparedStatement pstmt;
		try {
			
			pstmt = JDBCUtils.getConnection().prepareStatement(sql.toString());
			pstmt.setInt(1, userid);
			int row = pstmt.executeUpdate();
			if(row >= 0) {
				result = true;
			}else {
				result = false;
			}
			pstmt.close();	
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}
		
		return result;
	}

	@Override
	public List<User> selectNewUser() throws SQLException {
		// TODO Auto-generated method stub
		List<User> users = new ArrayList<User>();
		String sql = "SELECT * FROM \"d_user\" WHERE \"user_shinsa\"='0'";
		PreparedStatement pstmt;
		try {
			pstmt = JDBCUtils.getConnection().prepareStatement(sql);
			ResultSet rs  = pstmt.executeQuery();
			while(rs.next()){
				User user = new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9));
				users.add(user);
			}
			rs.close();
			pstmt.close();
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}	
		return users;
	}

	@Override
	public boolean updateShinsa(User user) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("UPDATE \"d_user\" SET ");
		boolean flag = false;
		sql.append(" \"user_shinsa\"='1'");
		int userid = user.getUserId();
		if(userid != 0) {
			sql.append(" WHERE \"user_id\"="+userid+"");
			flag = true;
		}
		if(!flag) {
			return false;
		}
	
		try {
			PreparedStatement pstmt = JDBCUtils.getConnection().prepareStatement(sql.toString());
			int row = pstmt.executeUpdate();
			if(row != 0) {
				flag = true;
			}else {
				flag = false;
			}
			pstmt.close();		
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.destroy();
		}
		return flag;
	}
	
}
